package com.examsys.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Paper;
import com.examsys.po.PaperDetail;
import com.examsys.po.PaperSection;
import com.examsys.po.Question;
/**
 * 试卷明细的数据访问层实现类
 * @author edu-1
 *
 */
public class PaperDetailDaoImpl extends AbstractBaseDao<PaperDetail, Integer> implements PaperDetailDao {

	/**
	 * 添加试卷明细
	 */
	@Override
	public void add(PaperDetail obj) throws Exception {
		String sql="Insert into PAPER_DETAIL(ID,PAPER_ID,PAPER_SECTION_ID,QUESTION_ID,SCORE,PORDER) values (PAPER_DETAIL_ID_SEQ.nextval,?,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getPaperSection().getId(),obj.getQuestion().getId(),obj.getScore(),obj.getPorder()});
	}

	/**
	 * 添加多条信息
	 * @param paperDetails
	 * @throws Exception
	 */
	public void add(List<PaperDetail> paperDetails) throws Exception{
		for(PaperDetail paperDetail:paperDetails){
			add(paperDetail);
		}
	}
	
	/**
	 * 修改试卷明细
	 */
	@Override
	public void update(PaperDetail obj) throws Exception {
		String sql="UPDATE PAPER_DETAIL SET PAPER_ID=?,PAPER_SECTION_ID=?,QUESTION_ID=?,SCORE=?,PORDER=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getPaperSection().getId(),obj.getQuestion().getId(),obj.getScore(),obj.getPorder(),obj.getId()});
	}

	/**
	 * 通过编号删除试卷明细
	 */
	@Override
	public void delete(Integer id) throws Exception {
		String sql="DELETE FROM PAPER_DETAIL WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 通过试卷编号删除试卷明细
	 */
	@Override
	public void deleteByPaperId(Integer id) throws Exception {
		String sql="DELETE FROM PAPER_DETAIL WHERE PAPER_ID=?";
		this.execute(sql, new Object[]{id});
	}
	
	/**
	 * 通过编号获取试卷明细
	 */
	@Override
	public PaperDetail get(Integer id) throws Exception {
		String sql="SELECT a.ID,a.PAPER_ID,a.PAPER_SECTION_ID,a.QUESTION_ID,a.SCORE,a.PORDER,b.PAPER_NAME,c.SECTION_NAME,d.CONTENT FROM PAPER_DETAIL a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN PAPER_SECTION c ON a.PAPER_SECTION_ID=c.ID LEFT JOIN  QUESTION d ON a.QUESTION_ID=d.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});
		
		Integer id1 =(Integer)m.get("ID");//编号
		Double score =(Double)m.get("SCORE");//本题分数
		Integer porder =(Integer)m.get("PORDER");//排序号
		
		Integer paper_id =(Integer)m.get("PAPER_ID");//试卷编号
		String  paper_name =(String)m.get("PAPER_NAME");//试卷名称
		
		Integer paper_section_id =(Integer)m.get("PAPER_SECTION_ID");//章节编号
		String  section_name =(String)m.get("SECTION_NAME");//章节名称
		
		Integer question_id =(Integer)m.get("QUESTION_ID");//试题编号
		String  content =(String)m.get("CONTENT");//试题题干
		
		//创建试卷明细实体类对象
		PaperDetail paperDetail=new PaperDetail();
		paperDetail.setId(id1);//编号
		paperDetail.setScore(score);//本题分数
		paperDetail.setPorder(porder);//排序号
		
		//创建试卷实体类对象
		Paper paper=new Paper();
		paper.setId(paper_id);//试卷编号
		paper.setPaper_name(paper_name);//试卷名称
		paperDetail.setPaper(paper);//设置关联对象
		
		//创建章节实体类对象
		PaperSection paperSection=new PaperSection();
		paperSection.setId(paper_section_id);//章节编号
		paperSection.setSection_name(section_name);//章节名称
		paperDetail.setPaperSection(paperSection);//设置关联对象
		
		//创建试题实体类对象
		Question question=new Question();
		question.setId(question_id);//试题编号
		question.setContent(content);//试题题干
		paperDetail.setQuestion(question);//设置关联对象
		
		return paperDetail;
	}

	/**
	 * 获取所有试卷明细记录
	 */
	@Override
	public List<PaperDetail> getList() throws Exception {
		String sql="SELECT a.ID,a.PAPER_ID,a.PAPER_SECTION_ID,a.QUESTION_ID,a.SCORE,a.PORDER,b.PAPER_NAME,c.SECTION_NAME,d.CONTENT FROM PAPER_DETAIL a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN PAPER_SECTION c ON a.PAPER_SECTION_ID=c.ID LEFT JOIN QUESTION d ON a.QUESTION_ID=d.ID";
		
		List<Map> resultList = this.query(sql, new Object[]{});
		List<PaperDetail> list=new ArrayList<PaperDetail>();
		
		for(Map m:resultList){
			Integer id1 =(Integer)m.get("ID");//编号
			Double score =(Double)m.get("SCORE");//本题分数
			Integer porder =(Integer)m.get("PORDER");//排序号
			
			Integer paper_id =(Integer)m.get("PAPER_ID");//试卷编号
			String  paper_name =(String)m.get("PAPER_NAME");//试卷名称
			
			Integer paper_section_id =(Integer)m.get("PAPER_SECTION_ID");//章节编号
			String  section_name =(String)m.get("SECTION_NAME");//章节名称
			
			Integer question_id =(Integer)m.get("QUESTION_ID");//试题编号
			String  content =(String)m.get("CONTENT");//试题题干
			
			//创建试卷明细实体类对象
			PaperDetail paperDetail=new PaperDetail();
			paperDetail.setId(id1);//编号
			paperDetail.setScore(score);//本题分数
			paperDetail.setPorder(porder);//排序号
			
			//创建试卷实体类对象
			Paper paper=new Paper();
			paper.setId(paper_id);//试卷编号
			paper.setPaper_name(paper_name);//试卷名称
			paperDetail.setPaper(paper);//设置关联对象
			
			//创建章节实体类对象
			PaperSection paperSection=new PaperSection();
			paperSection.setId(paper_section_id);//章节编号
			paperSection.setSection_name(section_name);//章节名称
			paperDetail.setPaperSection(paperSection);//设置关联对象
			
			//创建试题实体类对象
			Question question=new Question();
			question.setId(question_id);//试题编号
			question.setContent(content);//试题题干
			paperDetail.setQuestion(question);//设置关联对象
			
			list.add(paperDetail);//将试卷明细实体类对象放入集合中
		}
		return list;
	}

	/**
	 * 通过条件获取试卷明细
	 */
	@Override
	public List<PaperDetail> getList(PaperDetail obj) throws Exception {
		String sql="SELECT a.ID,a.PAPER_ID,a.PAPER_SECTION_ID,a.QUESTION_ID,a.SCORE,a.PORDER,b.PAPER_NAME,c.SECTION_NAME,d.CONTENT FROM PAPER_DETAIL a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN PAPER_SECTION c ON a.PAPER_SECTION_ID=c.ID LEFT JOIN QUESTION d ON a.QUESTION_ID=d.ID WHERE 1=1";
		
		if(obj.getPaper()!=null&&obj.getPaper().getId()!=null){
			sql+=" AND a.PAPER_ID="+obj.getPaper().getId();
		}
		
		if(obj.getPaperSection()!=null&&obj.getPaperSection().getId()!=null){
			sql+=" AND a.PAPER_SECTION_ID="+obj.getPaperSection().getId();
		}
		
		if(obj.getQuestion()!=null&&obj.getQuestion().getId()!=null){
			sql+=" AND a.QUESTION_ID="+obj.getQuestion().getId();
		}
		
		if(obj.getPaper()!=null&&obj.getPaper().getPaper_name()!=null){
			sql+=" AND b.PAPER_NAME LIKE '%"+obj.getPaper().getPaper_name()+"%'";
		}
		
		List<Map> resultList = this.query(sql, new Object[]{});
		List<PaperDetail> list=new ArrayList<PaperDetail>();
		
		for(Map m:resultList){
			Integer id1 =(Integer)m.get("ID");//编号
			Double score =(Double)m.get("SCORE");//本题分数
			Integer porder =(Integer)m.get("PORDER");//排序号
			
			Integer paper_id =(Integer)m.get("PAPER_ID");//试卷编号
			String  paper_name =(String)m.get("PAPER_NAME");//试卷名称
			
			Integer paper_section_id =(Integer)m.get("PAPER_SECTION_ID");//章节编号
			String  section_name =(String)m.get("SECTION_NAME");//章节名称
			
			Integer question_id =(Integer)m.get("QUESTION_ID");//试题编号
			String  content =(String)m.get("CONTENT");//试题题干
			
			//创建试卷明细实体类对象
			PaperDetail paperDetail=new PaperDetail();
			paperDetail.setId(id1);//编号
			paperDetail.setScore(score);//本题分数
			paperDetail.setPorder(porder);//排序号
			
			//创建试卷实体类对象
			Paper paper=new Paper();
			paper.setId(paper_id);//试卷编号
			paper.setPaper_name(paper_name);//试卷名称
			paperDetail.setPaper(paper);//设置关联对象
			
			//创建章节实体类对象
			PaperSection paperSection=new PaperSection();
			paperSection.setId(paper_section_id);//章节编号
			paperSection.setSection_name(section_name);//章节名称
			paperDetail.setPaperSection(paperSection);//设置关联对象
			
			//创建试题实体类对象
			Question question=new Question();
			question.setId(question_id);//试题编号
			question.setContent(content);//试题题干
			paperDetail.setQuestion(question);//设置关联对象
			
			list.add(paperDetail);//将试卷明细实体类对象放入集合中
		}
		return list;
	}

}
